package com.xdap.water.dao;

import com.definesys.mpaas.query.MpaasQuery;
import com.definesys.mpaas.query.MpaasQueryFactory;
import com.xdap.api.constant.CommonConstant;
import com.xdap.water.pojo.*;
import com.xdap.water.vo.WaterSearchParamVo;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@RequiredArgsConstructor
@Slf4j
public class WaterDao {
    @Autowired
    public MpaasQueryFactory sw;

    public List<ProjectTypePojo> queryAllProjectType(){
        return sw.buildQuery()
                .doQuery(ProjectTypePojo.class);
    }

    public List<ProjectItemPojo> queryProjectByParams(WaterSearchParamVo vo) {
        String sql = "SELECT id, document_id, procode, protype, proname, totalcost, alldone, ispromainpro, iscitymainpro, iszhpro, totalnt, levelone, leveltwo, yeartarget, proorg, responsible_unit_abbrev, cumulative_payment_amount, cumulative_progress_image, is_completed, isnormalpro, isbillonpro FROM t_gtpro WHERE 1 = 1";

        if (StringUtils.isNotBlank(vo.getUnit())) {
            sql += String.format(" AND proorg = '%s'", vo.getUnit());
        }

        if (StringUtils.isNotBlank(vo.getType())) {
            sql += String.format(" AND protypes = '[\"%s\"]'", vo.getType());
        }

        if (StringUtils.isNotBlank(vo.getYear())) {
            sql += String.format(" AND YEAR(proyear) = '%s'", vo.getYear());
        }

        if (StringUtils.isNotBlank(vo.getTopPhase())) {
            sql += String.format(" AND levelone = '%s'", vo.getTopPhase());
        }

        log.info("sql -> {}", sql);

        return sw.buildQuery().sql(sql).doQuery(ProjectItemPojo.class);
    }

    public List<ProjectReservePojo> queryProjectReserveByParams(WaterSearchParamVo vo) {
        String sql = "SELECT id, document_id, totalcost FROM t_tz_project WHERE 1 = 1";

        if (StringUtils.isNotBlank(vo.getUnit())) {
            sql += String.format(" AND orgname = '%s'", vo.getUnit());
        }

        if (StringUtils.isNotBlank(vo.getType())) {
            sql += String.format(" AND protypename = '[\"%s\"]'", vo.getType());
        }

        if (StringUtils.isNotBlank(vo.getYear())) {
            sql += String.format(" AND YEAR(years) = '%s'", vo.getYear());
        }

        log.info("sql -> {}", sql);

        return sw.buildQuery().sql(sql).doQuery(ProjectReservePojo.class);
    }

    public List<InvestmentPhasePojo> queryPhaseByTopPhase(String topPhase) {
        if (StringUtils.isNotBlank(topPhase)) {
            return sw.buildQuery()
                    .eq("parlevel", topPhase)
                    .orderBy("orderseq", CommonConstant.ASC)
                    .doQuery(InvestmentPhasePojo.class);
        }

        return sw.buildQuery()
                .isNull("parlevel")
                .orderBy("orderseq", CommonConstant.ASC)
                .doQuery(InvestmentPhasePojo.class);
    }

    public WaterProjectYearTaskPojo queryMinYear() {

        return sw.buildQuery()
                .orderBy("rwnd", CommonConstant.ASC)
                .doQueryFirst(WaterProjectYearTaskPojo.class);
    }

    public List<GtProjectXmlPojo> queryProjectType() {
        MpaasQuery mpaasQuery = sw.buildQuery();
        return sw.buildQuery()
                .doQuery(GtProjectXmlPojo.class);
    }

    public List<GtProjectUnitPojo> queryAllUnit() {
        return sw.buildQuery()
                .doQuery(GtProjectUnitPojo.class);
    }

    public List<WaterProjectYearTaskPojo> queryCurrentYearProjectYearTask() {
        String sql = "SELECT * FROM gt_project_nd WHERE YEAR(rwnd) = YEAR(NOW())";

        log.info("sql -> {}", sql);
        return sw.buildQuery().sql(sql).doQuery(WaterProjectYearTaskPojo.class);
    }

    public GtFixedInvestCompleteStatusPojo queryFixedInvestCompleteStatus(String date) {
        String sql = String.format("SELECT * FROM gt_wcqk_z WHERE DATE(tb_date) = \"%s\"", date);

        log.info("sql -> {}", sql);
        return sw.buildQuery().sql(sql).doQueryFirst(GtFixedInvestCompleteStatusPojo.class);
    }

    public List<GtUnitCompleteStatusPojo> queryUnitCompleteStatusListByTabDocId(String docId) {
        return sw.buildQuery()
                .eq("tab_doc_id", docId)
                .doQuery(GtUnitCompleteStatusPojo.class);
    }

    public GtFixedInvestCompleteStatusPojo queryMaxDate() {
        return sw.buildQuery()
                .orderBy("tb_date", CommonConstant.DESC)
                .doQueryFirst(GtFixedInvestCompleteStatusPojo.class);
    }

    public List<WaterProjectYearTaskPojo> queryWaterProjectByYearAndUnit(String unit, String year) {
        String sql = "SELECT * FROM gt_project_nd WHERE 1 = 1";
        if (StringUtils.isNotBlank(unit)) {
            sql += String.format(" AND zrdw = '[\"%s\"]'", unit);
        }

        if (StringUtils.isNotBlank(year)) {
            sql += String.format(" AND YEAR(rwnd) = \"%s\"", year);
        }

        log.info("sql -> {}", sql);

        return sw.buildQuery().sql(sql).doQuery(WaterProjectYearTaskPojo.class);
    }

    public List<WaterProjectYearTaskPojo> queryProjectYearTaskByYearAndIds(String year, List<String> projectIds) {
        String sql = String.format("SELECT * FROM gt_project_nd WHERE YEAR(rwnd) = \"%s\" AND pro_name IN (%s);", year, String.join(",", projectIds));
        log.info("sql => {}", sql);

        return sw.buildQuery().sql(sql).doQuery(WaterProjectYearTaskPojo.class);
    }

    public List<GtProjectInformationPojo> queryProjectInformationListById(List<String> ids) {
        return sw.buildQuery().in("id", ids).doQuery(GtProjectInformationPojo.class);
    }

    public List<GtFillProjectPojo> queryFillProjectByMaxDate(List<String> ids, String start, String end) {

        String sql = "SELECT * FROM gt_project_tb WHERE (pro_name, tb_date) IN (" +
                "SELECT pro_name, MAX(tb_date) FROM gt_project_tb WHERE" +
                String.format(" (tb_date BETWEEN \"%s\" AND \"%s\")", start, end) +
                String.format(" AND pro_name IN (%s)", String.join(",", ids)) +
                " GROUP BY pro_name)";
        log.info("sql -> {}", sql);
        return sw.buildQuery().sql(sql).doQuery(GtFillProjectPojo.class);
    }

    public List<GtProjectInformationPojo> queryProjectInformationListByUnitId(String unitId) {
        return sw.buildQuery().eq("pro_zrdw", unitId).doQuery(GtProjectInformationPojo.class);
    }

    public List<GtProjectInvestSchedulePojo> queryProjectInvestSchedule(String year) {
        String sql = String.format("SELECT * FROM gt_project_tzjd WHERE YEAR(tz_date) = \"%s\";", year);
        log.info("sql => {}", sql);
        return sw.buildQuery().sql(sql).doQuery(GtProjectInvestSchedulePojo.class);
    }

    public List<GtProjectInvestSchedulePojo> queryProjectInvestSchedule(String year, List<String> projectIds) {
        String sql = String.format("SELECT * FROM gt_project_tzjd WHERE YEAR(tz_date) = \"%s\" AND pro_name IN (%s);", year, String.join(",", projectIds));
        log.info("sql => {}", sql);
        return sw.buildQuery().sql(sql).doQuery(GtProjectInvestSchedulePojo.class);
    }

    public List<GtFillProjectPojo> queryFillProject(String start, String end) {
        String sql = String.format("SELECT * FROM gt_project_tb WHERE tb_date BETWEEN \"%s\" AND \"%s\";", start, end);
        log.info("sql => {}", sql);
        return sw.buildQuery().sql(sql).doQuery(GtFillProjectPojo.class);
    }

    public List<GtFillProjectPojo> queryFillProject(String start, String end, List<String> projectIds) {
        String sql = String.format("SELECT * FROM gt_project_tb WHERE (tb_date BETWEEN \"%s\" AND \"%s\") AND pro_name IN (%s);", start, end, String.join(",", projectIds));
        log.info("sql => {}", sql);
        return sw.buildQuery().sql(sql).doQuery(GtFillProjectPojo.class);
    }
}
